# coding=utf-8

"""

# 注意：不要设置 ODBCINI 环境变量，如果有，需要 py 里 os.unsetenv("ODBCINI")
export GBASEDBTDIR=/opt/gbase8s-odbc-driver
export CSDK_HOME=/opt/gbase8s-odbc-driver
export PATH=$GBASEDBTDIR/bin:$PATH
export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql:$LD_LIBRARY_PATH

# add sqlhostfile
mkdir -p $GBASEDBTDIR/etc
echo "gbase01 onsoctcp 192.168.3.225 9088" > $GBASEDBTDIR/etc/sqlhosts

"""

"""
# download  need SQLAlchemy-1.3.8

https://gbasedbt.com/dl/odbc/GBase8s_3.0.0_1-Linux64-ODBC-Driver.tar.xz
https://pypi.org/project/DbtPy/#files
https://pypi.org/project/sqlalchemy-gbasedbt/0.2.4/#files

"""

# run: python3 gbas8s_sqlalchemy.py

from sqlalchemy import MetaData, Table, Column, String, create_engine
from sqlalchemy.dialects import registry
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
registry.register("gbasedbt", "sqlalchemy_gbasedbt.dbtdb", "GBasedbtDialect")

import os
os.unsetenv('ODBCINI')


# 创建对象的基类:
Base = declarative_base()
# 定义 User 对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'
    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
# 初始化数据库连接:
# ConStr = 'gbasedbt://<username>:<password>@<host name>:<port number>/<databasename>;SERVER=<server name>'
# 注意：hostname:port 基本无意义，直接 SERVER 匹配 GBASEDBTSQLHOSTS 配置文件中的 GBASEDBTSERVER
USER = 'test'
PASSWORD = 'Gbase@123'
DATABASE = 'test'
SERVER = 'gbase01'

ConStr = 'gbasedbt://{user}:{password}@/{db};SERVER={server};DLOC=zh_CN.utf8;CLOC=zh_CN.utf8;DELIMIDENT=y'.format(
    user=USER, password=PASSWORD, db=DATABASE, server=SERVER
)

engine = create_engine(ConStr)
# 删除对象
Base.metadata.drop_all(engine)
# 创建对象
Base.metadata.create_all(engine)

# 创建 DBSession 类型:
DBSession = sessionmaker(bind=engine)

def init_data():
    # 创建 session 对象:
    session = DBSession()
    # 创建新 User 对象:
    new_user = User(id='2', name='测试用户 a20512')
    # 添加到 session:
    session.add(new_user)
    # 提交即保存到数据库:
    session.commit()
    # 关闭 session:
    session.close()



def test():
    # 创建 Session:
    session = DBSession()
    user = session.query(User).filter(User.id=='2').one_or_none()
    # 打印类型和对象的 name 属性:
    print('Print user')
    if user :
        print('type:', type(user))
        print('name:', user.name)
    # 执行原生 SQL 语句
    sqlstr = "select tabid,tabname from systables where tabid > :id and owner = :myuser"
    params = {"id" : 1, "myuser": USER}
    result_proxy = session.execute(sqlstr, params)
    result = result_proxy.fetchall()
    print('\nPrint tables of user:')
    for item in result:
        print(item)
    # 关闭 Session:
    session.close()

if __name__ == '__main__':
    init_data()
    test()
